import time

import mysql.connector
import requests

'''
抓取汇率并保存到数据库完成
https://finance.sina.com.cn/money/forex/hq/USDCNY.shtml
'''


# 创建链接数据库
config_168 = {'host': '127.0.0.1',  # 默认127.0.0.1
          'user': 'root', 'password': 'Tnq39/*riqJcC', 'port': 3306,  # 默认即为3306
          'database': 'ccii_redesign', 'charset': 'utf8','auth_plugin':'mysql_native_password'  # 默认即为utf8
          }

config1 = {'host': 'rm-2zefd6473rz062234.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_ccii', 'password': 'cI1546_wodesecerts', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8', 'auth_plugin':'mysql_native_password'  # 默认即为utf8
          }


config = {'host': 'rm-2zefd6473rz062234.mysql.rds.aliyuncs.com',  # 默认127.0.0.1
          'user': 'prod_python', 'password': '939_58J6kAW)P&^', 'port': 3306,  # 默认即为3306
          'database': 'ccii_prod', 'charset': 'utf8'  # 默认即为utf8
          }


headers = {'Accept': 'text/html, application/xhtml+xml, image/jxr, */*',
               'Accept - Encoding':'gzip, deflate',
               'Accept-Language':'zh-Hans-CN, zh-Hans; q=0.5',
               'Connection':'Keep-Alive',
               'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36 Edge/15.15063'
           }

base_prefix = "var hq_str_"
base_url = "https://hq.sinajs.cn/rn=1605071726893list="

# 美元、欧元、澳元、越南盾
url_suffixs =[
    "fx_susdcny",
    "fx_seurcny",
    "fx_saudcny",
    "fx_svndcny"
]

# 汇率名称
rate_names =[
    ["USDCNY", "人民币兑美元"],
    ["EURCNY", "人民币兑欧元"],
    ["AUDCNY", "人民币兑澳元"],
    ["VNDCNY", "人民币兑越南盾"]
]

# 查询数据库当天已存在数据列表
def find_name_List(price_date):
    cnn = ''
    try:
        cnn = mysql.connector.connect(**config)  # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
    except mysql.connector.Error as e:
        print('数据库链接失败！', str(e))
    else:  # try没有异常的时候才会执行
        print("连接数据库sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        stmt = 'select rate_en_name from alarm_rate_cny  where date_format(worm_date, "%Y-%m-%d") = %s '
        cursor.execute(stmt, [price_date])
        data = cursor.fetchall()
        # cnn.commit()
        # print("查询数据成功！")

    except mysql.connector.Error as e:
        print('查询数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接

    return data


'''
获取汇率数据
'''
def get_rate(url):

    rate = 0
    request_url = base_url + url
    print("请求完整地址=" + request_url)
    replace_str = base_prefix + url
    print("替换前缀=" + replace_str)

    try:
        response = requests.get(url=request_url, headers=headers)
        response.raise_for_status()
        resultStr = response.text
        print("请求返回结果=" + resultStr)
        if len(resultStr) > 0:
            resultStr = resultStr.replace(replace_str, "")
            arr = resultStr.split(",")
            rate = arr[8]
            # print(arr[8])
        else :
            print("返回结果为空")

    except Exception as e:
        print("请求异常！")
        print(f"Unexpected error: {e}")

    return rate



'''
保存到数据库
'''
def save(data):
    cnn=''
    try:
        # connect方法加载config的配置进行数据库的连接，完成后用一个变量进行接收
        cnn=mysql.connector.connect(**config)
    except mysql.connector.Error as e:
        print('数据库链接失败！',str(e))
    else:
        #try没有异常的时候才会执行
        print("sucessfully!")

    # 插入数据库
    cursor = cnn.cursor(buffered=True)  # 获取插入的标记位
    try:

        # 第三种：可以一次插入多条，效率比一条条插高,用的方法是executemany 猪牛羊 1 2 3
        stmt = 'insert into alarm_rate_cny (rate_value, rate_en_name, rate_cn_name, create_date, worm_date, del_flag) values (%s, %s, %s, NOW(), NOW(), 0)'
        # for item in data:
        #     print(item)
        #     cursor.execute(stmt, item)
        cursor.executemany(stmt, data)
        cnn.commit()
        print("插入数据成功！")

    except mysql.connector.Error as e:
        print('插入数据报错！', str(e))
    finally:  # 无论如何都会执行下面的语句
        cursor.close()  # 关闭标记位
        cnn.close()  # 关闭数据库链接


def main():
    print(" 开始爬取数据" + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
    sqlData = []
    for i, item in enumerate(url_suffixs):
        data = list()
        data.append(get_rate(item))
        data.extend(rate_names[i])
        print(data)
        sqlData.append(data)


    sql_data_copy = list(sqlData)

    worm_date = time.strftime("%Y-%m-%d", time.localtime())
    db_name = find_name_List(str(worm_date))
    db_name_list = list()

    if db_name:
        for item in db_name:
            db_name_list.append(list(item)[0])

        print(db_name_list)

    for item in sql_data_copy:
        if item[1] in db_name_list:
            print("{} -->在数据库已存在，移除该条记录不保存。".format(item[1]))
            sqlData.remove(item)
        else:
            print("不存在--执行保存")


    if sqlData :
        save(sqlData)
    else:
        print("data为空，数据未保存")

    print("爬取数据完成！" + time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))



if __name__ == '__main__':
        main()
